﻿WITH cteColoriAbbinati(IDMovimento, IDPolvere, IDFamiglia) AS 
(
	select m.idmovimento, cc.idpolvere, ac.idfamiglia
	FROM  [GP].[Movimenti] AS M
	inner join gp.composizionecolori as cc on m.idpolvere = cc.idpolvere
	inner join gp.anagcolori as ac on cc.idcolore = ac.idcolore 
)
SELECT m.idmovimento,
Stuff(( select distinct ',' + cast(Sub.IDFamiglia as varchar) AS [text()] from cteColoriAbbinati as Sub where Sub.IDPolvere = M.IdPolvere for xml path('')), 1, 1, '') as 'Famiglie'
FROM  [GP].[Movimenti] as M


-----------------------------------------------
Per aggiornare la tabella movimenti, eseguire la seguente UPDATE:

WITH cteColoriAbbinati(IDMovimento, IDPolvere, IDFamiglia) AS 
(
	select m.idmovimento, cc.idpolvere, ac.idfamiglia
	FROM  [GP].[Movimenti] AS M
	inner join gp.composizionecolori as cc on m.idpolvere = cc.idpolvere
	inner join gp.anagcolori as ac on cc.idcolore = ac.idcolore 
)
update [GP].[Movimenti]
set Famiglie = Stuff(( select distinct ',' + cast(Sub.IDFamiglia as varchar) AS [text()] from cteColoriAbbinati as Sub where Sub.IDPolvere = M.IdPolvere for xml path('')), 1, 1, '')
from [GP].[Movimenti] as M
join cteColoriAbbinati as cte on M.idmovimento = cte.idmovimento